{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "aklJxkHBD5aR"
   },
   "source": [
    "# Introduction to BigQuery ML - Predict Birth Weight\n",
    "\n",
    "**Learning Objectives**\n",
    "\n",
    "\n",
    "1. Use BigQuery to explore the natality dataset\n",
    "1. Create a regression (linear regression) model in BQML\n",
    "1. Evaluate the performance of your machine learning model\n",
    "1. Make predictions with a trained BQML model\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "## Introduction \n",
    "\n",
    "In this lab, you will be using the US Centers for Disease Control and Prevention's (CDC) natality data to build a model to predict baby birth weights based on a handful of features known at pregnancy. Because we're predicting a continuous value, this is a regression problem, and for that, we'll use the linear regression model built into BQML."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Set up the notebook environment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__VERY IMPORTANT__:  In the cell below you must replace the text `<YOUR PROJECT>` with your GCP project id as provided during the setup of your environment. Please leave any surrounding single quotes in place."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "BhUiclqCD5aT"
   },
   "outputs": [],
   "source": [
    "PROJECT = '<YOUR PROJECT>' #TODO Replace with your GCP PROJECT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "j782YaAzD5ae"
   },
   "source": [
    "## Exploring the Data\n",
    "\n",
    "This lab will use natality data and training on features to predict the birth weight.\n",
    "\n",
    "The CDC's Natality data has details on US births from 1969 to 2008 and is available in BigQuery as a public data set. More details: https://bigquery.cloud.google.com/table/publicdata:samples.natality?tab=details\n",
    "\n",
    "Start by looking at the data since 2000 with useful values, those greater than 0.\n",
    "\n",
    "Note:  \"__%%bigquery__\" is a magic which allows quick access to BigQuery from within a notebook."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TODO 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "odWaIcLdD5af",
    "outputId": "2654ad9b-a735-4cd3-c3d9-80ebed8ed6bf"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source_year</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>day</th>\n",
       "      <th>wday</th>\n",
       "      <th>state</th>\n",
       "      <th>is_male</th>\n",
       "      <th>child_race</th>\n",
       "      <th>weight_pounds</th>\n",
       "      <th>plurality</th>\n",
       "      <th>...</th>\n",
       "      <th>alcohol_use</th>\n",
       "      <th>drinks_per_week</th>\n",
       "      <th>weight_gain_pounds</th>\n",
       "      <th>born_alive_alive</th>\n",
       "      <th>born_alive_dead</th>\n",
       "      <th>born_dead</th>\n",
       "      <th>ever_born</th>\n",
       "      <th>father_race</th>\n",
       "      <th>father_age</th>\n",
       "      <th>record_weight</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>12</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>CO</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.063611</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>32.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>6</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>IN</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>4.687028</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>38.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>4</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>MN</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.561856</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>40.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>99</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>MS</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.561856</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>33.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>3</td>\n",
       "      <td>MO</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.312733</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>10</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>NY</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>7.627994</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>11</td>\n",
       "      <td>None</td>\n",
       "      <td>5</td>\n",
       "      <td>WA</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.251004</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>99.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>2</td>\n",
       "      <td>OK</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>7.500126</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>47.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>4</td>\n",
       "      <td>TX</td>\n",
       "      <td>False</td>\n",
       "      <td>9</td>\n",
       "      <td>7.125340</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>35.0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>2001</td>\n",
       "      <td>2001</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>TX</td>\n",
       "      <td>True</td>\n",
       "      <td>9</td>\n",
       "      <td>7.749249</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   source_year  year  month   day  wday state  is_male  child_race  \\\n",
       "0         2001  2001     12  None     3    CO     True           9   \n",
       "1         2001  2001      6  None     5    IN     True           9   \n",
       "2         2001  2001      4  None     5    MN     True           9   \n",
       "3         2001  2001     10  None     5    MS     True           9   \n",
       "4         2001  2001     11  None     3    MO     True           9   \n",
       "5         2001  2001     10  None     5    NY    False           9   \n",
       "6         2001  2001     11  None     5    WA     True           9   \n",
       "7         2001  2001      9  None     2    OK    False           9   \n",
       "8         2001  2001      1  None     4    TX    False           9   \n",
       "9         2001  2001      1  None     1    TX     True           9   \n",
       "\n",
       "   weight_pounds  plurality  ...  alcohol_use  drinks_per_week  \\\n",
       "0       7.063611          1  ...        False             None   \n",
       "1       4.687028          3  ...        False             None   \n",
       "2       7.561856          1  ...        False             None   \n",
       "3       7.561856          1  ...        False             None   \n",
       "4       7.312733          1  ...        False             None   \n",
       "5       7.627994          1  ...        False             None   \n",
       "6       7.251004          1  ...         None             None   \n",
       "7       7.500126          1  ...        False             None   \n",
       "8       7.125340          1  ...        False             None   \n",
       "9       7.749249          1  ...        False             None   \n",
       "\n",
       "  weight_gain_pounds  born_alive_alive  born_alive_dead  born_dead ever_born  \\\n",
       "0               32.0                 0                0          0         1   \n",
       "1               38.0                 0                0          0         1   \n",
       "2               40.0                 0                0          0         1   \n",
       "3               33.0                 0                0          0         1   \n",
       "4                NaN                 1                0          1         2   \n",
       "5                NaN                 2                0          0         3   \n",
       "6               99.0                 3                0          1         4   \n",
       "7               47.0                 1                0          1         2   \n",
       "8               35.0                 3                0          0         4   \n",
       "9               36.0                 0                0          0         1   \n",
       "\n",
       "   father_race father_age record_weight  \n",
       "0            1         39             1  \n",
       "1            1         33             1  \n",
       "2            9         99             1  \n",
       "3            1         31             1  \n",
       "4            1         32             1  \n",
       "5            1         33             1  \n",
       "6            1         32             1  \n",
       "7            2         25             1  \n",
       "8            1         30             1  \n",
       "9            1         32             1  \n",
       "\n",
       "[10 rows x 31 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    *\n",
    "FROM\n",
    "  publicdata.samples.natality\n",
    "WHERE\n",
    "  year > 2000\n",
    "    AND gestation_weeks > 0\n",
    "    AND mother_age > 0\n",
    "    AND plurality > 0\n",
    "    AND weight_pounds > 0\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "ep02J20pD5ai"
   },
   "source": [
    "## Define Features\n",
    "\n",
    "Looking over the data set, there are a few columns of interest that could be leveraged into features for a reasonable prediction of approximate birth weight.\n",
    "\n",
    "Further, some feature engineering may be accomplished with the BigQuery `CAST` function -- in BQML, all strings are considered categorical features and all numeric types are considered continuous ones.\n",
    "\n",
    "The hashmonth is added so that we can repeatably split the data without leakage -- the goal is to have all babies that share a birthday to be either in training set or in test set and not spread between them (otherwise, there would be information leakage when it comes to triplets, etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "DPUCYDjlD5aj",
    "outputId": "3cbc1dbe-22d0-406c-fe9c-0a019040ae3d"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>weight_pounds</th>\n",
       "      <th>is_male</th>\n",
       "      <th>mother_age</th>\n",
       "      <th>plurality</th>\n",
       "      <th>gestation_weeks</th>\n",
       "      <th>hashmonth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6.686620</td>\n",
       "      <td>true</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>43</td>\n",
       "      <td>8904940584331855459</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>9.360828</td>\n",
       "      <td>true</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>1088037545023002395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>8.437091</td>\n",
       "      <td>false</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "      <td>5896567601480310696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>6.124442</td>\n",
       "      <td>false</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>6244544205302024223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>7.125340</td>\n",
       "      <td>false</td>\n",
       "      <td>26</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "      <td>8029892925374153452</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>6.944561</td>\n",
       "      <td>false</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>2126480030009879160</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>6</td>\n",
       "      <td>6.999677</td>\n",
       "      <td>true</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1088037545023002395</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>7</td>\n",
       "      <td>9.374055</td>\n",
       "      <td>true</td>\n",
       "      <td>34</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1525201076796226340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>8</td>\n",
       "      <td>8.373157</td>\n",
       "      <td>true</td>\n",
       "      <td>33</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>3408502330831153141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>9</td>\n",
       "      <td>7.187070</td>\n",
       "      <td>false</td>\n",
       "      <td>23</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "      <td>1403073183891835564</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   weight_pounds is_male  mother_age plurality  gestation_weeks  \\\n",
       "0       6.686620    true          18         1               43   \n",
       "1       9.360828    true          32         1               41   \n",
       "2       8.437091   false          30         1               39   \n",
       "3       6.124442   false          24         1               40   \n",
       "4       7.125340   false          26         1               41   \n",
       "5       6.944561   false          31         1               40   \n",
       "6       6.999677    true          23         1               40   \n",
       "7       9.374055    true          34         1               40   \n",
       "8       8.373157    true          33         1               40   \n",
       "9       7.187070   false          23         1               40   \n",
       "\n",
       "             hashmonth  \n",
       "0  8904940584331855459  \n",
       "1  1088037545023002395  \n",
       "2  5896567601480310696  \n",
       "3  6244544205302024223  \n",
       "4  8029892925374153452  \n",
       "5  2126480030009879160  \n",
       "6  1088037545023002395  \n",
       "7  1525201076796226340  \n",
       "8  3408502330831153141  \n",
       "9  1403073183891835564  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "    weight_pounds, -- this is the label; because it is continuous, we need to use regression\n",
    "    CAST(is_male AS STRING) AS is_male,\n",
    "    mother_age,\n",
    "    CAST(plurality AS STRING) AS plurality,\n",
    "    gestation_weeks,\n",
    "    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth\n",
    "FROM\n",
    "    publicdata.samples.natality\n",
    "WHERE\n",
    "    year > 2000\n",
    "    AND gestation_weeks > 0\n",
    "    AND mother_age > 0\n",
    "    AND plurality > 0\n",
    "    AND weight_pounds > 0\n",
    "LIMIT 10"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "H-d7C8KcD5am"
   },
   "source": [
    "## Train Model\n",
    "\n",
    "With the relevant columns chosen to accomplish predictions, it is then possible to create and train the model in BigQuery. First, a dataset will be needed store the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "bWMZJQdKD5an"
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "bq --location=US mk -d demo"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "T9JZIQ9nD5ap"
   },
   "source": [
    "With the demo dataset ready, it is possible to create a linear regression model to train the model.\n",
    "\n",
    "This will take approximately **5 to 7 minutes** to run.  Feedback from BigQuery will cease in output cell and the notebook will leave the \"busy\" state when complete."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "V-UJOX0tD5aq",
    "outputId": "63aa219c-a14f-4db5-a271-2e614759fe1a"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: []"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "CREATE or REPLACE MODEL demo.babyweight_model_asis\n",
    "OPTIONS\n",
    "  (model_type='linear_reg', labels=['weight_pounds'], optimize_strategy='batch_gradient_descent') AS\n",
    "WITH natality_data AS (\n",
    "  SELECT\n",
    "    weight_pounds,-- this is the label; because it is continuous, we need to use regression\n",
    "    CAST(is_male AS STRING) AS is_male,\n",
    "    mother_age,\n",
    "    CAST(plurality AS STRING) AS plurality,\n",
    "    gestation_weeks,\n",
    "    FARM_FINGERPRINT(CONCAT(CAST(YEAR AS STRING), CAST(month AS STRING))) AS hashmonth\n",
    "  FROM\n",
    "    publicdata.samples.natality\n",
    "  WHERE\n",
    "    year > 2000\n",
    "    AND gestation_weeks > 0\n",
    "    AND mother_age > 0\n",
    "    AND plurality > 0\n",
    "    AND weight_pounds > 0\n",
    ")\n",
    "\n",
    "SELECT\n",
    "    weight_pounds,\n",
    "    is_male,\n",
    "    mother_age,\n",
    "    plurality,\n",
    "    gestation_weeks\n",
    "FROM\n",
    "    natality_data\n",
    "WHERE\n",
    "  ABS(MOD(hashmonth, 4)) < 3  -- select 75% of the data as training"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "mJYtGxWyD5at"
   },
   "source": [
    "## Training Statistics"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "0L92s-gtD5au"
   },
   "source": [
    "For all training runs, statistics are captured in the \"TRAINING_INFO\" table. This table has basic performance statistics for each iteration.\n",
    "\n",
    "The query below returns the training details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "ZbV6aDcRD5au",
    "outputId": "9518b192-2f7f-4b69-e612-2e3068f9925c"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.130779</td>\n",
       "      <td>1.130047</td>\n",
       "      <td>0.4</td>\n",
       "      <td>34755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.132380</td>\n",
       "      <td>1.131636</td>\n",
       "      <td>0.8</td>\n",
       "      <td>41029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.143513</td>\n",
       "      <td>1.143927</td>\n",
       "      <td>0.4</td>\n",
       "      <td>43320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.179019</td>\n",
       "      <td>1.179471</td>\n",
       "      <td>0.4</td>\n",
       "      <td>37349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.572833</td>\n",
       "      <td>1.576313</td>\n",
       "      <td>0.4</td>\n",
       "      <td>38108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9.855745</td>\n",
       "      <td>9.846956</td>\n",
       "      <td>0.2</td>\n",
       "      <td>37631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration      loss  eval_loss  learning_rate  duration_ms\n",
       "0             0          5  1.130779   1.130047            0.4        34755\n",
       "1             0          4  1.132380   1.131636            0.8        41029\n",
       "2             0          3  1.143513   1.143927            0.4        43320\n",
       "3             0          2  1.179019   1.179471            0.4        37349\n",
       "4             0          1  1.572833   1.576313            0.4        38108\n",
       "5             0          0  9.855745   9.846956            0.2        37631"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT * FROM ML.TRAINING_INFO(MODEL demo.babyweight_model_asis);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "CATWu60vD5ay"
   },
   "source": [
    "Some of these columns are obvious although what do the non-specific ML columns mean (specific to BQML)?\n",
    "\n",
    "**training_run** - Will be zero for a newly created model. If the model is re-trained using warm_start, this will increment for each re-training.\n",
    "\n",
    "**iteration** - Number of the associated `training_run`, starting with zero for the first iteration.\n",
    "\n",
    "**duration_ms** - Indicates how long the iteration took (in ms).\n",
    "\n",
    "Next plot the training and evaluation loss to see if the model has an overfit."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery history \n",
    "\n",
    "SELECT * FROM ML.TRAINING_INFO(MODEL demo.babyweight_model_asis)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>training_run</th>\n",
       "      <th>iteration</th>\n",
       "      <th>loss</th>\n",
       "      <th>eval_loss</th>\n",
       "      <th>learning_rate</th>\n",
       "      <th>duration_ms</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1.130779</td>\n",
       "      <td>1.130047</td>\n",
       "      <td>0.4</td>\n",
       "      <td>34755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.132380</td>\n",
       "      <td>1.131636</td>\n",
       "      <td>0.8</td>\n",
       "      <td>41029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1.143513</td>\n",
       "      <td>1.143927</td>\n",
       "      <td>0.4</td>\n",
       "      <td>43320</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1.179019</td>\n",
       "      <td>1.179471</td>\n",
       "      <td>0.4</td>\n",
       "      <td>37349</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1.572833</td>\n",
       "      <td>1.576313</td>\n",
       "      <td>0.4</td>\n",
       "      <td>38108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>9.855745</td>\n",
       "      <td>9.846956</td>\n",
       "      <td>0.2</td>\n",
       "      <td>37631</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   training_run  iteration      loss  eval_loss  learning_rate  duration_ms\n",
       "0             0          5  1.130779   1.130047            0.4        34755\n",
       "1             0          4  1.132380   1.131636            0.8        41029\n",
       "2             0          3  1.143513   1.143927            0.4        43320\n",
       "3             0          2  1.179019   1.179471            0.4        37349\n",
       "4             0          1  1.572833   1.576313            0.4        38108\n",
       "5             0          0  9.855745   9.846956            0.2        37631"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "history"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAX4AAAEGCAYAAABiq/5QAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4xLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy8QZhcZAAAgAElEQVR4nO3deXxU9b3/8ddnJgkhELYQ2QIEkSiIAkpQoaJXlFr1Ihirtrbupbe919raavVn76/2/mzrr/bXVq9erddq65Uq1ri0te6V4oJCwEQ2AdnDmoUlC9lmvr8/ZsSAgAnJzDcz8372kWbmzMnM+xB5z+F7zvmOOecQEZHUEfAdQERE4kvFLyKSYlT8IiIpRsUvIpJiVPwiIikmzXeAtujfv7/Lz8/3HUNEJKEsXry40jmXe/DyhCj+/Px8SkpKfMcQEUkoZrbxUMs11CMikmJU/CIiKSZmxW9mj5rZTjNb1mpZPzN7zczWRL/3jdXri4jIocVyjP/3wP3A462W3Qa84Zy728xui97/YQwziEgX19zcTHl5OQ0NDb6jJKzMzEzy8vJIT09v0/oxK37n3Hwzyz9o8cXA2dHbfwDmoeIXSWnl5eVkZ2eTn5+PmfmOk3Ccc1RVVVFeXs6IESPa9DPxHuMf4JzbFr29HRhwuBXNbLaZlZhZSUVFRftfaf0ceD4f/hiIfF8/52jyikiMNTQ0kJOTo9I/SmZGTk5Ou/7F5O3grotMC3rYqUGdcw875yY65ybm5n7mNNQjWz8HFs6G+o2Rl6jfGLmv8hfpklT6HdPeP794F/8OMxsEEP2+MyavUnYHhOoPXBaqjywXEUlx8S7+PwNXR29fDbwQk1ep3wTAH/fCBVtgY/OBy0VEWuvZs6fvCHEVy9M5nwQWAMebWbmZXQ/cDZxnZmuAc6P3O1/WMACeq4OX6uHZ2gOXi0gC0/G7DotZ8TvnvuKcG+ScS3fO5Tnnfuecq3LOTXPOjXLOneucq47Ji4/7KQSzuDT6Jl5cCwSzIstFJHHF+Pidc45bbrmFsWPHctJJJzF37lwAtm3bxtSpUxk/fjxjx47lrbfeIhQKcc011+xf99e//nWnZIiHhJirp91GXAnABUtuo9uOct5tgG3HfZ9B0eUi0kX98SgO8obqYcHXIl+H89W2fcTss88+S2lpKWVlZVRWVlJYWMjUqVP54x//yBe/+EXuuOMOQqEQ9fX1lJaWsmXLFpYti1yjunv37vZn9yR5p2wYcSXZRZv5Yv88HPDcllLfiUSki3v77bf5yle+QjAYZMCAAZx11lksWrSIwsJCHnvsMe68806WLl1KdnY2xx57LOvWrePGG2/k5ZdfplevXr7jt1ly7vG3UjS6iD9X3Evx2nl823cYETmyz9szfz4/OsxzkKzhMHNDLBIBMHXqVObPn8+LL77INddcw80338xVV11FWVkZr7zyCg899BBPP/00jz76aMwydKbk3eOP+ufCH1KQYRQGa3A163zHEZGOiB6/O0AnHr8788wzmTt3LqFQiIqKCubPn8+kSZPYuHEjAwYM4Bvf+AY33HADS5YsobKyknA4TFFREXfddRdLlizplAzxkPR7/H17DmLVmZfBprlQ/hyM/r7vSCJytD45Tld2R+T07KxhkdLvpON3s2bNYsGCBYwbNw4z4xe/+AUDBw7kD3/4A/fccw/p6en07NmTxx9/nC1btnDttdcSDocB+PnPf94pGeLBIhfQdm0TJ050Hfoglk1/grcvg/5nwPR3Oy+YiHTYypUrGT16tO8YCe9Qf45mttg5N/HgdZN+qAeAQV9ia7gb93+8gN3VK3ynERHxKjWKP70nX6/K5sYK+MvC2FwzJiKSKFKj+IFLCi4AoHjN3zwnERHxK2WKf9ak2wB4ZVcVtXvXe04jIuJPyhT/4JzRTO7VhwYHf1uk4R4RSV0pU/wARcdNB6D4o9hMCioikghSqvgvmRT5lMcXq3awr26r5zQiIn6kVPHnDziFM7J7cWZ3qFj3lO84IpJk8vPzqaysPOzjXWXe/5QqfoC3LvgpLw2BYdXzfEcREfEi6adsOFhweBEs+Q5sexWaayA923ckEWnFfnL4qZl/e9FvmX3qbAAeXvww3/zrNw+7rvtx+2YleOKJJ7jvvvtoamritNNO4+STT2bDhg3cc889APz+97+npKSE+++/n5kzZ7J582YaGhq46aabmD17drteyznHrbfeyksvvYSZ8aMf/YjLL7+cbdu2cfnll7N3715aWlp48MEHmTx5Mtdffz0lJSWYGddddx3f+9732vV6B0u5PX66DyLUfzLzahtZtuwh32lEpAtYuXIlc+fO5Z133qG0tJRgMEjPnj157rnn9q8zd+5crrjiCgAeffRRFi9eTElJCffddx9VVVXter3W8/6//vrr3HLLLWzbtm3/vP+fPDZ+/PgD5v1funQp1157bYe3N+X2+AF+UduH/7UFrg//F49MuMV3HBFppa176rNPnb1/77+j3njjDRYvXkxhYSEA+/bt45hjjuHYY4/lvffeY9SoUXz00UdMmTIFgPvuu2//m8LmzZtZs2YNOTk5bX69I837f91119Hc3MzMmTMZP378AfP+X3jhhUyfPr3D25t6e/zAP59yEwDP79xAS9Nez2lExDfnHFdffTWlpaWUlpayatUq7rzzTq644gqefvppiouLmTVrFmbGvHnzeP3111mwYAFlZWVMmDCBhoaGTsnxybz/Q4YM4ZprruHxxx+nb9++lJWVcfbZZ/PQQw9xww03dPh1UrL4Txx+LgWZmVSF4B8fJM7nZIpIbEybNo1nnnmGnTt3AlBdXc3GjRuZNWsWL7zwAk8++eT+YZ49e/bQt29fsrKy+Oijj3jvvffa/Xq+5/1PyaEeM+PS/DP42UdvUrxsDtNO+7HvSCLi0ZgxY7jrrruYPn064XCY9PR0HnjgAYYPH87o0aNZsWIFkyZNAuD888/noYceYvTo0Rx//PGcfvrp7X493/P+p8Z8/Iew5OMXOHXOTAamGeW31hJMz/r8HxKRTqf5+DuH5uNvgwkjZ5Cfkc72Fse7H97vO46ISNyk5FAPRIZ7ioYX8vT6d6ksfwVOvdV3JBFJAlVVVUybNu0zy9944412nfkTSylb/AD/cd693PNqIdZSBuEWCKT0H4eIN845zA5/4VYiycnJobS0NK6v2d4h+5Qd6gHI6n8q1vt4aKyCnf/wHUckJWVmZlJVVdXu8pII5xxVVVVkZma2+WdSexfXDIYWsaPsZ2xY+l+cNvCz/zwTkdjKy8ujvLyciooK31ESVmZmJnl5eW1eP7WLH/gg80QmrocRW15gzbQQFgj6jiSSUtLT0xkxYoTvGCklpYd6AE4+7jL6pwVY2xTiw4/+x3ccEZGYS/niDwbTmDl4LADPlD7oOY2ISOylfPEDXDruOgCKNy0BHWASkSSn4gfOPvlf6Bs0Vja2sPLjP/mOIyISUyp+ID2tGxcPOh6A4iX/6TmNiEhsqfijik66iiCwvfJDDfeISFJT8UdNn/BddpyQw/1998LuD33HERGJGRV/VEZ6d3LyL4nc2VzsN4yISAx5KX4z+56ZLTezZWb2pJm1/VrjWBpaRNjBB6ue8J1ERCRm4l78ZjYE+A4w0Tk3FggCV8Q7x6GEjzmLgo0BTlmxns3lb/qOIyISE76GetKA7maWBmQBWz3lOEAgLZPxfYcC8OyiX3pOIyISG3EvfufcFuCXwCZgG7DHOffqweuZ2WwzKzGzknhO3lQ05lIAitdrtk4RSU4+hnr6AhcDI4DBQA8z+9rB6znnHnbOTXTOTczNzY1bvgsLbyXD4O2aOrbveD9urysiEi8+hnrOBdY75yqcc83As8BkDzkOqVfWMXwxZzAOeH7hL3zHERHpdD6KfxNwupllWeQjd6YBKz3kOKyiE2YBULz2Dc9JREQ6n48x/veBZ4AlwNJohofjneNIZky6jTRgdd0eGveu9R1HRKRTeTmrxzn3Y+fcCc65sc65rzvnGn3kOJy+2XmUTZrG+nzotu1vvuOIiHQqXbl7GGNOuJaAoat4RSTpqPgPZ8hFEEincvt86ms2+E4jItJpVPyHk9GbW+ryGLjW8ex7/+E7jYhIp1HxH8HwQWcQAopXveg7iohIp1HxH8GsSbcD8HL1TmprN3tOIyLSOVT8RzCk/1jOyO5Ng4OXFt3tO46ISKdQ8X+OouOmAVC88nnPSUREOoeK/3NcUvhDAF6s3Mq++h2e04iIdJyK/3OMGDSJU3r0oN5BydL/8h1HRKTDVPxt8Lsp32LbCDizabnvKCIiHabib4PxJ9/IMWnA1pegpd53HBGRDlHxt0WPYdCvkHBLPTWbnvOdRkSkQ1T8bfRyxliGb4Cb3/ix7ygiIh2i4m+jIflFlLfA8zvW0dJc5zuOiMhRU/G30dgRFzCqWwaVIcdbpff5jiMictRU/G1kZhQNPw2A4qWPe04jInL0VPztUHTqvwHw7LZVhENd6rNjRETaTMXfDqeO+jLDM9LY1uJYsPRB33FERI6Kir8dzIyioacC8KKGe0QkQan42+lbk2/nH3nwf7K2QDjkO46ISLup+NvpuBEzmNp/BMHGnVD5ru84IiLtpuJvLzMYVgRAy8Y/eQ4jItJ+Kv6jsCPnnzh/C4x740FcOOw7johIu6j4j0L/IeexpDHAisYWlq5+0nccEZF2UfEfhWAwnZmDRwPwzAcPeE4jItI+Kv6jVHTy1QAUbyoB5zynERFpOxX/UTpn3I30DRorGpr5aN0LvuOIiLSZiv8opadnMmPgKACKl9zrOY2ISNup+DugaOyVADy74T3PSURE2k7F3wHnnfI9HhjUnb8OaIA9K33HERFpExV/B2RmZPPtky5jUBqwudh3HBGRNlHxd9TQyFW8btMznoOIiLSNir+jBp3Hv1enM/KDMsq3veM7jYjI51Lxd1Qwk+WBAaxvgWcX/sJ3GhGRz6Xi7wSXjr4EgOK1b3pOIiLy+VT8neCiSbeRYfBWTQ07Kj7wHUdE5Ii8FL+Z9TGzZ8zsIzNbaWZn+MjRWXr1GMR5/QbigOcX3u07jojIEfna478XeNk5dwIwDkj4k+CLjp8BQPHHr3pOIiJyZHEvfjPrDUwFfgfgnGtyzu2Od47ONmPS7QSBv+/eTVV1wr+PiUgS87HHPwKoAB4zsw/M7BEz6+EhR6fK6Z3Pr0aO4dUh0LtCB3lFpOvyUfxpwCnAg865CUAdcNvBK5nZbDMrMbOSioqKeGc8Kt854weckwVpW573HUVE5LB8FH85UO6cez96/xkibwQHcM497Jyb6JybmJubG9eARy1vBlgQdrwJjdW+04iIHFLci985tx3YbGbHRxdNA1bEO0dMdMvh5fSTuKi8haff+ZHvNCIih+TrrJ4bgTlm9iEwHviZpxydbk1mAS/Ww1Mrn/MdRUTkkLwUv3OuNDqMc7JzbqZzbpePHLFwyaTI4YqXq7ZTV7fNcxoRkc9qU/Gb2U1m1ssifmdmS8xseqzDJaIhx0zg9J7Z7HPw0qL/6zuOiMhntHWP/zrn3F5gOtAX+DqgS1QPo2jkPwFQvFJz9ItI19PW4rfo9wuA/3HOLW+1TA5SVHgrAH+tLKehQWf3iEjX0tbiX2xmrxIp/lfMLBsIxy5WYhsxZAoTsrKoDcOri+/xHUdE5ABpbVzveiJn36xzztWbWT/g2tjFSnw/OGkGleueYlLjKt9RREQO0NbiPwModc7VmdnXiFxwdW/sYiW+r065C6qfgsrXIdQIwW6+I4mIAG0f6nkQqDezccD3gbXA4zFLlQyyR0KfcdBSA9tf851GRGS/thZ/i3POARcD9zvnHgCyYxcrOWzKmcYPK+Enb/677ygiIvu1tfhrzOx2IqdxvmhmASA9drGSw+6cL/CLXfDAhjJCLQ2+44iIAG0v/suBRiLn828H8gCdrvI5Tho5k+O6pVMRcrxVdr/vOCIiQBuLP1r2c4DeZnYR0OCc0xj/5zAzioZNBKB46e/9hhERiWrrlA2XAQuBLwOXAe+b2aWxDJYsiiZ8G4DiLSsJh5o9pxERaftQzx1AoXPuaufcVcAkQEcs22Di8V9lWHqQbS1h3lv+377jiIi0ufgDzrmdre5XteNnU5oFAhQNHQ9AcekjntOIiLT9Aq6XzewV4Mno/cuBv8UmUvL5yin/Sm3ldcwMlIMLg+k9U0T8aVPxO+duMbMiYEp00cPOOX3SSBsVjrmawjX/G+rLoWoR9D/NdyQRSWFt3ePHOVcMaJ7ho2EByLsEVt8Hm4tV/CLi1RHHHMysxsz2HuKrxsz2xitkMmgaMoP/2QvfevchXFgTm4qIP0csfudctnOu1yG+sp1zveIVMhkEcr/AzZXGQ5U1LFurfziJiD86yhgnaWndmDnoeACKF/+n5zQikspU/HFUdNLXASjeuNBzEhFJZSr+ODpnwk30CRjLGhpZtV5nw4qIHyr+OMpI78GMgccCULz4157TiEiqUvHHWdGJXwGgeP07npOISKpS8cfZ9FN/wDlZQa7M2ke45mPfcUQkBan44yyzW2/emHwZN/eFwGZd/Cwi8afi92FoUeT75mf85hCRlKTi92Hw+ZQ1d+NHqxayZfsi32lEJMWo+H1I68Gdtf346S54duHdvtOISIpR8Xty6ehZABSvfcNzEhFJNSp+Ty4qvI104K29e9hZucx3HBFJISp+T3pnD+W8frmEgecX/dx3HBFJISp+j4oKLgKgePXLnpOISCpR8Xt08Wm3EwT+vrua6t26mEtE4kPF71FOn1EU9R/A13tB3aYXfMcRkRSh4vds7vSf8OgAGFr9pu8oIpIiVPy+5c0EDLa/Bs36NEsRiT1vxW9mQTP7wMz+6itDl9B9AHv7ncGTe5p4e8n/851GRFKAzz3+m4CVHl+/y3isaQBf3Q6/WfyI7ygikgK8FL+Z5QEXAmo64JJJtwHwUuVW6up3ek4jIsnO1x7/b4BbgfDhVjCz2WZWYmYlFRUV8UvmwdCBk5jUowf1Dl4uucd3HBFJcnEvfjO7CNjpnFt8pPWccw875yY65ybm5ubGKZ0/l448C4DiFX/ynEREkp2PPf4pwAwz2wA8BZxjZk94yNGlFBXeAsBfKzbS2Kize0QkduJe/M65251zec65fOAK4O/Oua/FO0dXc2ze2YzP6k5NGF5b8kvfcUQkiek8/i7k0hGTKewGgcoFvqOISBLzWvzOuXnOuYt8ZuhKbj/3XhYOgwsaF0O42XccEUlS2uPvQgJ9ToReo6FpF+yY5zuOiCQpFX8X4/IuYWkj/KVEV/GKSGyo+LuYFT0ncPImuOHDVwm1NPmOIyJJSMXfxYw5dhYjM9LY2eJ4e+mDvuOISBJS8XcxFghQNOwUAJ4pe9RzGhFJRir+Lqho/DcBeHbLMsLhFs9pRCTZqPi7oMLRVzM0PcjWljDvr3jMdxwRSTIq/i7IAkGK8k4CoLj0Yc9pRCTZqPi7qKJx19M7AGk1a8A533FEJImo+LuoyWO/yc7Rudzdew/sKvUdR0SSiIq/iwoE08kYVhS5s7nYbxgRSSoq/q5sWBG1YfjL0t/jNNwjIp1Exd+FudypjNkUYMbaLSxf92ffcUQkSaj4uzALZjD9mJEAFC++13MaEUkWKv4urmjslQAUb9Ac/SLSOVT8Xdy0U26mdwCW7mtg9abXfccRkSSg4u/iMjKy+edj8gEoXqSpmkWk41T8CeDSEy8HoHj9fM9JRCQZqPgTwPRTb6FnAFxLPfW7V/uOIyIJTsWfALp3z2H95ItYPAyytr/kO46IJDgVf4LoP/KrkRu6ildEOkjFnyiGXAiBDFZueYvq6pW+04hIAlPxJ4r0XtxYM5gxG+GP797pO42IJDAVfwI5bcR5ABR//KrnJCKSyFT8CeSiwttJB+bv2U3FrlW+44hIglLxJ5A+vUdwbt8cwsDz7//MdxwRSVAq/gRTVHABAMWr/+Y5iYgkKhV/grm48DaCwBu7Ktm1Z6PvOCKSgFT8CaZ/zhjO6t2HTIOlKx/xHUdEEpCKPwE9NvUmKo6FqU3LfUcRkQSk4k9Aw064gcwAsO1laKnzHUdEEoyKPxFl5UHOaexr3seWNU/4TiMiCUbFn6BeTDuR3HXw3Xk/9R1FRBKMij9BnTT6Wuoc/K1iM/UN1b7jiEgCUfEnqGGDv0BhjyzqHbxS8kvfcUQkgaj4E9ilI84E4JnlT3lOIiKJJO7Fb2ZDzexNM1thZsvN7KZ4Z0gWRRNvBuCvO9fT2FTrOY2IJAofe/wtwPedc2OA04F/NbMxHnIkvJHDpzOuezf2huH1D37tO46IJIi4F79zbptzbkn0dg2wEhgS7xzJoij/dADeXfOs5yQikii8jvGbWT4wAXj/EI/NNrMSMyupqKiId7SE8Y3J/86q4fDTrK0QDvmOIyIJwFvxm1lPoBj4rnNu78GPO+ceds5NdM5NzM3NjX/ABDFwyDkU9BsJDTuh4m3fcUQkAXgpfjNLJ1L6c5xzGqPoCDMYWgRA9do5nsOISCLwcVaPAb8DVjrnfhXv109Glf2nMW4jjHnzd4RCzb7jiEgX52OPfwrwdeAcMyuNfl3gIUfSyBlyLrUE2dES5p1l/+07joh0cT7O6nnbOWfOuZOdc+OjX/o4qQ6wQICivPEAFJdpjn4ROTJduZskisZ/A4Di8g8J6+weETkCFX+SKBxzHXlpxpbmEAsfS4Pn82G9DvaKyGep+JNEYNPTFPWM3H6iBqjfCAtnq/xF5DPSfAeQTlJ2B0U9HffuhoKM6LJQPX/5+w280+MRCnJPpGDQaRTknU1udh6Rk6tEJBWp+JNF/SamZMJV2TA969PFL+5t4Lfl82DVPOABAHoHjFFZ2Uzpn89vTr8eskdBdgG16Tn0zOzjI72IxJGKP1lkDSNQv5E/DDxw8WV9ezJ44Ims3r2J1bXVrGpoZE/YUVK7l+zQh7A4MjnqvjD0WgsD0oIU9OjNqN6DKeg3ioJjxlMweArHDZlCRlqmhw0Tkc6m4k8W434aGdMP1X+6LJjFOWc9xDkjrty/yLU0UlG5mNXlbxHYVw7dmqFmDZsrl9PNdrC9JcT2PdXM31MNm5YBzwHwwpAMZgw+HrILeH1fOiuajYIBp1KQdybDc08hGNR/SiKJQn9bk8Un5V52B9RvgqxhkTeDVqUPYGndOGbgZI4ZOPmA5QVAXXMt5dveYfXWd1i9s4w11R+zes82VtfvpSCtCXYvhd1LmbsDHtkL8CQAGQYju2UyqmcOkwecwA8nXhcdPhqFS++t4wkiXYw553xn+FwTJ050JSUlvmOktqbdULMGatbw9Irn+PvWUlbX7GB1fS1bWj79b+i8LHg1Osl2bRgGrzeO655FQXYuBX1HUJA7llEDJ1GQ90/0zdZs3CKxZGaLnXMTP7NcxS8d4hx1NRv4eMs/WL1tIdnNVZyfFYKaNZRVrGL8hsbD/uhLI/pz/pCTIXsUCxoz2EIWBYMnc9yQqWTpILNIh6n4Jf5cmKrq5awp/werty9mddVKVu/exJraalY3NFI6DEZFTz29bgc81mpy7qHpaZGDzL0Gc9rAk7lm/FWR4aOsYRAIfrri+jmfO7wlkqpU/NKluFAz1G3EaiPDRw98WMwr21eyum4XaxtbaGm17vlZ8FJ0VGivS6dwszGqZ18KMrtT0LKZUWkh+gQgzWBUZjeyxv0Uhs2iqmEvdS1NBAPppAUzCAa7RW9nkp6WSWZ6D7BgZGrrRJKKb3apts2dtL0qfkkYLc21bNj6Fmu2LmD1zjIGU8eXe7RAzRoW79rKxM2H/9n3hsJp0bNOv70THtxz6PVOzoCy4dE7FiBzTRhH5GyHoEEQI80gaMbdg/pwTU5vsCDFe+r50dZK0ixA0Cy6nhEMRO6/edKppAXSwYJ8f90qPm6oJ2gBghYgzYLR9QKc3W8I1w4dAxZkS2MD96xbQlogSNDSIt8DQYIWJC2QxjUjJjEkqx9YkHlrX2L5ptcJuhABAwPMggRyp5DTfzwXDz01+kZmzFn3Lpjxyf8CgSBmhhHglJxjGdl7EGBsqKmgtHo9ZgECFti/jplhZpyfV4hZAMxYtHM1e5vrCVgQO2jdgVk5jOozDMyoa25gWfX6/Y9Hnjew/2cK+gwnKz0LzNhWV8muxtrIY1irHAEy0zLJ21MCS38EoQY2fjLreLAbnHALNmj6/t9pv+596JnRA4Dapjp2NbT+5R/45p6XPWj/SQc766toDrVwKFkZWfTN7A1AU6iJyn27Dvl8AP279yUjmAEYexr3sq+l4RDPaKQH08np3nf/kh11FfsfA7CtL8Gy/4BQA9kB6B4Aglkw6eF2l//hil9n9UiXk5bek+OGf4njhn+JLx302EkN1Swt/zurty5g9Ye/YnUzfNwEtQ5aHPT85O9jj3z6dKsiL72OkIOQc7QQ+R4CegQsUpAuDC5MY3T/pwnA7f8/wNHQUA111QBU18JHh/r7HBXY8eb+TvhHBSw+zCGOjNq1XNs4H4AdDXDvEd7Mpu99jSHRN7OndsBvP/N5dSHYMZ/x3eZz8ZZPl35tzeGf88FcGBk9jPLaHpi98/Drho779B9F39p0+G26vhc8MiBye1UDnH6EbVo4FAqj23TnDnj4M9sUMb4bfDDs0/sjNnzym2mEtXcBdx2wTf8S3aYn27FNF26CksNs0w294L+j27SsAU5t4zb98JC/p4gJ3WBJq20atObT/9oOtn+bQvWRfwF00r9yVPySUDIy+zH2uEsZe9ylUF0cmZPoYFnD4eL1/Az42ec9oXPgQjS1NBAKNxIKNdESaiIUaiQUjtzuld4d0rtBOMSX91UzpXZ75LFwE6FQS3S9ZkLhZgKDTox89rEL8autZexq3BtZL9xCKNxCS7iZULiFUT1zITcfwiGG7NvFr/qV0OJaouuFaAm3EHIhQuEQg/PHQbfu4EKcVXMfAYu8yTkO/Bra/2TIH7d/yZU17xB2DofDRb9H7sOxg/Oh3zGAY3jWDmaE1u9fL0xknU9+xgaeEn0zcxTWrKTXvjoc7H+uyM/BqD79IHcIOEdWfR2F1R8f8Hjr583qPRy6ZwKOQVSgg4sAAAXfSURBVHXbOaFpz/71XHS9sIOhmRlA3f5f17C01m/JRIbqonp07ws9siO3m2rJS68+/O+9R160+R25GTsYHD7EBxg56NMtC7Iie+fp1sSgtAoOV9Pp3XOhezoAvbrtZkBa/SHX65eeDt37779/TNq2T7fJAYT3P5bZeja1+k2H35520lCPJK71cw550drR/JM4YTyff/g3u5kb4p0mPlJtmztxew831KPZOSVxjbgyUvJZwwGLfE/m0ofIQb5g1oHLglmR5ckq1bY5DturoR5JbCOuTO6iP1gbr9BOKqm2zXHYXg31iIgkKQ31iIgIoOIXEUk5Kn4RkRSj4hcRSTEqfhGRFJMQZ/WYWQVwiCsa2qQ/UNmJcRKBtjk1aJuTX0e3d7hzLvfghQlR/B1hZiWHOp0pmWmbU4O2OfnFans11CMikmJU/CIiKSYViv9h3wE80DanBm1z8ovJ9ib9GL+IiBwoFfb4RUSkFRW/iEiKSeriN7PzzWyVmX1sZrf5zhNrZvaome00s2W+s8SDmQ01szfNbIWZLTezm3xnijUzyzSzhWZWFt3mn/jOFC9mFjSzD8zsr76zxIOZbTCzpWZWamadOj1x0o7xm1kQWA2cB5QDi4CvOOdWeA0WQ2Y2FagFHnfOjfWdJ9bMbBAwyDm3xMyygcXAzCT/HRvQwzlXa2bpwNvATc659zxHizkzuxmYCPRyzl3kO0+smdkGYKJzrtMvWEvmPf5JwMfOuXXOuSbgKeBiz5liyjk3HzjCB40mF+fcNufckujtGmAlMMRvqthyEbXRu+nRr+Tce2vFzPKAC4FHfGdJBslc/EOAza3ul5PkpZDKzCwfmAC87zdJ7EWHPEqBncBrzrmk32bgN8CttP4k8uTngFfNbLGZze7MJ07m4pcUYWY9gWLgu865vb7zxJpzLuScGw/kAZPMLKmH9czsImCnc26x7yxx9gXn3CnAl4B/jQ7ldopkLv4twNBW9/OiyySJRMe5i4E5zrlnfeeJJ+fcbuBN4HzfWWJsCjAjOub9FHCOmT3hN1LsOee2RL/vBJ4jMnzdKZK5+BcBo8xshJllAFcAf/acSTpR9EDn74CVzrlf+c4TD2aWa2Z9ore7Ezl54SO/qWLLOXe7cy7POZdP5O/x351zX/McK6bMrEf0hAXMrAcwHei0s/WStvidcy3AvwGvEDno97RzbrnfVLFlZk8CC4DjzazczK73nSnGpgBfJ7IHWBr9usB3qBgbBLxpZh8S2bl5zTmXEqc3ppgBwNtmVgYsBF50zr3cWU+etKdziojIoSXtHr+IiByail9EJMWo+EVEUoyKX0Qkxaj4RURSjIpfUoqZvRv9nm9mX+3k5/5fh3otka5Gp3NKSjKzs4EftGeWRzNLi14fcrjHa51zPTsjn0gsaY9fUoqZfTKz5d3AmdGLvr4XnfjsHjNbZGYfmtk3o+ufbWZvmdmfgRXRZc9HJ85a/snkWWZ2N9A9+nxzWr+WRdxjZsui86tf3uq555nZM2b2kZnNiV6NLBJTab4DiHhyG632+KMFvsc5V2hm3YB3zOzV6LqnAGOdc+uj969zzlVHp0xYZGbFzrnbzOzfopOnHewSYDwwDugf/Zn50ccmACcCW4F3iFyN/Hbnb67Ip7THLxIxHbgqOt3x+0AOMCr62MJWpQ/wneil9O8RmQhwFEf2BeDJ6KyaO4B/AIWtnrvcORcGSoH8TtkakSPQHr9IhAE3OudeOWBh5FhA3UH3zwXOcM7Vm9k8ILMDr9vY6nYI/Z2UONAev6SqGiC71f1XgG9Fp3nGzAqisyIerDewK1r6JwCnt3qs+ZOfP8hbwOXR4wi5wFQiE2+JeKG9C0lVHwKh6JDN74F7iQyzLIkeYK0AZh7i514G/sXMVgKriAz3fOJh4EMzW+Kcu7LV8ueAM4AyIp+qdKtzbnv0jUMk7nQ6p4hIitFQj4hIilHxi4ikGBW/iEiKUfGLiKQYFb+ISIpR8YuIpBgVv4hIivn/6x+0Fuv6fXcAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.plot('iteration', 'loss', data=history,\n",
    "         marker='o', color='orange', linewidth=2)\n",
    "\n",
    "plt.plot('iteration', 'eval_loss', data=history,\n",
    "         marker='', color='green', linewidth=2, linestyle='dashed')\n",
    "\n",
    "plt.xlabel('iteration')\n",
    "plt.ylabel('loss')\n",
    "plt.legend();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "Add--LfeD5a2"
   },
   "source": [
    "As you can see, the training loss and evaluation loss are essentially identical. There does not appear to be any overfitting."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9QPwMA_ZD5a3"
   },
   "source": [
    "## Make a Prediction with BQML using the Model"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "9QBZxMb6D5a5"
   },
   "source": [
    "With a trained model, it is now possible to make a prediction on the values. The only difference from the second query above is the reference to the model. The data has been limited (`LIMIT 100`) to reduce amount of data returned.\n",
    "\n",
    "When the `ml.predict` function is leveraged, output prediction column name for the model is `predicted_<label_column_name>`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "autoexec": {
      "startup": false,
      "wait_interval": 0
     }
    },
    "colab_type": "code",
    "id": "x-hedqSeD5a6",
    "outputId": "e9bbeb43-900c-420a-8520-2feeccf79239"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>predicted_weight_pounds</th>\n",
       "      <th>weight_pounds</th>\n",
       "      <th>is_male</th>\n",
       "      <th>mother_age</th>\n",
       "      <th>plurality</th>\n",
       "      <th>gestation_weeks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>7.845635</td>\n",
       "      <td>6.311835</td>\n",
       "      <td>true</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>7.324174</td>\n",
       "      <td>7.231162</td>\n",
       "      <td>false</td>\n",
       "      <td>31</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>7.316675</td>\n",
       "      <td>6.680007</td>\n",
       "      <td>true</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>7.177865</td>\n",
       "      <td>5.967913</td>\n",
       "      <td>false</td>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>7.208956</td>\n",
       "      <td>8.212219</td>\n",
       "      <td>true</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>95</td>\n",
       "      <td>7.017631</td>\n",
       "      <td>8.062305</td>\n",
       "      <td>true</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>96</td>\n",
       "      <td>7.198766</td>\n",
       "      <td>6.999677</td>\n",
       "      <td>false</td>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>97</td>\n",
       "      <td>7.504788</td>\n",
       "      <td>7.061406</td>\n",
       "      <td>true</td>\n",
       "      <td>27</td>\n",
       "      <td>1</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>98</td>\n",
       "      <td>6.798427</td>\n",
       "      <td>7.500126</td>\n",
       "      <td>false</td>\n",
       "      <td>18</td>\n",
       "      <td>1</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>99</td>\n",
       "      <td>7.515499</td>\n",
       "      <td>7.813183</td>\n",
       "      <td>false</td>\n",
       "      <td>28</td>\n",
       "      <td>1</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    predicted_weight_pounds  weight_pounds is_male  mother_age plurality  \\\n",
       "0                  7.845635       6.311835    true          19         1   \n",
       "1                  7.324174       7.231162   false          31         1   \n",
       "2                  7.316675       6.680007    true          18         1   \n",
       "3                  7.177865       5.967913   false          24         1   \n",
       "4                  7.208956       8.212219    true          25         1   \n",
       "..                      ...            ...     ...         ...       ...   \n",
       "95                 7.017631       8.062305    true          28         1   \n",
       "96                 7.198766       6.999677   false          25         1   \n",
       "97                 7.504788       7.061406    true          27         1   \n",
       "98                 6.798427       7.500126   false          18         1   \n",
       "99                 7.515499       7.813183   false          28         1   \n",
       "\n",
       "    gestation_weeks  \n",
       "0                41  \n",
       "1                39  \n",
       "2                39  \n",
       "3                39  \n",
       "4                38  \n",
       "..              ...  \n",
       "95               37  \n",
       "96               39  \n",
       "97               39  \n",
       "98               38  \n",
       "99               40  \n",
       "\n",
       "[100 rows x 6 columns]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT\n",
    "  *\n",
    "FROM\n",
    "  ml.PREDICT(MODEL demo.babyweight_model_asis,\n",
    "      (SELECT\n",
    "        weight_pounds,\n",
    "        CAST(is_male AS STRING) AS is_male,\n",
    "        mother_age,\n",
    "        CAST(plurality AS STRING) AS plurality,\n",
    "        gestation_weeks\n",
    "      FROM\n",
    "        publicdata.samples.natality\n",
    "      WHERE\n",
    "        year > 2000\n",
    "        AND gestation_weeks > 0\n",
    "        AND mother_age > 0\n",
    "        AND plurality > 0\n",
    "        AND weight_pounds > 0\n",
    "    ))\n",
    "LIMIT 100"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "PK_-WNGUD5bX"
   },
   "source": [
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "<br>\n",
    "Copyright 2018 Google Inc. Licensed under the Apache License, Version 2.0 (the \"License\"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an \"AS IS\" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "default_view": {},
   "name": "babyweight_bqml.ipynb",
   "provenance": [],
   "version": "0.3.2",
   "views": {}
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
